<?php
session_start();
require_once "include/database.php";
$db = db_connect();
require_once "include/auth.php";
$current_user = current_user();
require_login();
require_user(0);

$title = "CS 4400 Project | Student Report";

include "_header.php"; 

$query = $db->prepare("SELECT SC.Instructor, GROUP_CONCAT(DISTINCT SC.Code SEPARATOR ' / ') AS Code, 
                      SC.Title, AVG(CASE WHEN (-1*(ASCII(reg.Grade_Received) - 69) < 0) THEN 0 ELSE 
                      ABS(ASCII(reg.Grade_Received) - 69) END) AS Average FROM registers AS reg
                      LEFT JOIN SectionComplete AS SC on reg.CRN = SC.CRN GROUP BY Title");

$query->execute();

echo "<div class='container'>";
echo "<h2 style='text-align:center;'> Student Report </h2>";
echo "<table class='table table-bordered table-hover'>";
echo "<thead style='background-color:darkgrey'><tr>" .
    "<td>Instructor</td>" .
    "<td>Course Code</td>" . 
    "<td>Course Name</td>" .
    "<td>Average Grade</td>" .
    "</tr></thead>";
while($result = $query->fetch()) {
    echo "<tr>";
    echo "<td>" . $result['Instructor'] . "</td> <td>" .
    $result['Code'] . "</td> <td>" .
    $result['Title'] . "</td> <td>" .
    $result['Average'] . "</td>";
    echo "</tr>";
}

echo "</table></div>";

?>
<?php include "_footer.php";?>